package lulin.w09.dao;

import lulin.w09.entity.Department;
import lulin.w09.entity.School;
import lulin.w09.entity.Teacher;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.util.List;

@Repository
public class TeacherDao implements TeacherDaoInterface {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public Long addTeacher(Teacher teacher) {
        String sql = "INSERT INTO teacher (name, department_id) VALUES (?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});
            ps.setString(1, teacher.getName());
            ps.setLong(2, teacher.getDepartmentId());
            return ps;
        }, keyHolder);
        return keyHolder.getKey().longValue();
    }

    @Override
    public int deleteTeacher(Long id) {
        return jdbcTemplate.update("DELETE FROM teacher WHERE id = ?", id);
    }

    @Override
    public int updateTeacher(Teacher teacher) {
        return jdbcTemplate.update("UPDATE teacher SET name = ?, department_id = ? WHERE id = ?",
                teacher.getName(), teacher.getDepartmentId(), teacher.getId());
    }

    @Override
    public Teacher getTeacher(Long id) {
        Teacher teacher = jdbcTemplate.queryForObject("SELECT * FROM teacher WHERE id = ?",
                new BeanPropertyRowMapper<>(Teacher.class), id);
        if (teacher != null) {
            Department department = jdbcTemplate.queryForObject("SELECT * FROM department WHERE id = ?",
                    new BeanPropertyRowMapper<>(Department.class), teacher.getDepartmentId());
            if (department != null) {
                School school = jdbcTemplate.queryForObject("SELECT * FROM school WHERE id = ?",
                        new BeanPropertyRowMapper<>(School.class), department.getSchoolId());
                department.setSchool(school);
            }
            teacher.setDepartment(department);
        }
        return teacher;
    }

    @Override
    public List<Teacher> getAllTeachers() {
        List<Teacher> teachers = jdbcTemplate.query("SELECT * FROM teacher",
                new BeanPropertyRowMapper<>(Teacher.class));
        for (Teacher teacher : teachers) {
            Department department = jdbcTemplate.queryForObject("SELECT * FROM department WHERE id = ?",
                    new BeanPropertyRowMapper<>(Department.class), teacher.getDepartmentId());
            if (department != null) {
                School school = jdbcTemplate.queryForObject("SELECT * FROM school WHERE id = ?",
                        new BeanPropertyRowMapper<>(School.class), department.getSchoolId());
                department.setSchool(school);
            }
            teacher.setDepartment(department);
        }
        return teachers;
    }

    @Override
    public List<Teacher> getTeachersBySchoolId(Long schoolId) {
        String sql = "SELECT t.* FROM teacher t " +
                "JOIN department d ON t.department_id = d.id " +
                "JOIN school s ON d.school_id = s.id " +
                "WHERE s.id = ?";
        List<Teacher> teachers = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Teacher.class), schoolId);
        for (Teacher teacher : teachers) {
            Department department = jdbcTemplate.queryForObject("SELECT * FROM department WHERE id = ?",
                    new BeanPropertyRowMapper<>(Department.class), teacher.getDepartmentId());
            if (department != null) {
                School school = jdbcTemplate.queryForObject("SELECT * FROM school WHERE id = ?",
                        new BeanPropertyRowMapper<>(School.class), department.getSchoolId());
                department.setSchool(school);
            }
            teacher.setDepartment(department);
        }
        return teachers;
    }

    @Override
    public List<Teacher> getTeachersByDepartmentId(Long departmentId) {
        List<Teacher> teachers = jdbcTemplate.query("SELECT * FROM teacher WHERE department_id = ?",
                new BeanPropertyRowMapper<>(Teacher.class), departmentId);
        for (Teacher teacher : teachers) {
            Department department = jdbcTemplate.queryForObject("SELECT * FROM department WHERE id = ?",
                    new BeanPropertyRowMapper<>(Department.class), teacher.getDepartmentId());
            if (department != null) {
                School school = jdbcTemplate.queryForObject("SELECT * FROM school WHERE id = ?",
                        new BeanPropertyRowMapper<>(School.class), department.getSchoolId());
                department.setSchool(school);
            }
            teacher.setDepartment(department);
        }
        return teachers;
    }
}    